using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class InvoiceItems : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable GetInvoiceItems(long nInvoiceID)
		{
			DataTable dataTable = new DataTable("InvoiceItem");
            //string cmdText = "select p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit as 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',ii.Quantity AS 数量,dbo.ItemSquare(p.Width,p.Height,ii.Quantity) as 面积,CAST(p.Price as decimal(9, 2)) AS 价格,p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺,c.CompanyName AS 客户,pj.PKID AS 计划ID,pj.ProjectName AS 计划 FROM InvoiceItem AS ii inner join Product AS p on ii.ProductID=p.PKID inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID inner join Customer AS c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE ii.InvoiceID=@InvoiceID order by pt.[Sequence],convert( int, substring(p.PKID,1,6) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('.',p.PKID)+1,CHARINDEX('-',p.PKID)-CHARINDEX('.',p.PKID)-1) ), CONVERT( INT, SUBSTRING(p.PKID,CHARINDEX('-',p.PKID)+1,LEN(p.PKID)) )";
            string cmdText = @"SELECT p.PKID AS 产品ID, pt.[Name] AS 类型, pt.Unit AS 单位, p.ProductCode AS 编号, p.Width AS 宽
	                            , p.SubtenseOfWidth AS '宽(短边)', p.Height AS 高, p.SubtenseOfHeight AS '高(短边)', ii.Quantity AS 数量
	                            , dbo.ItemSquare(p.Width, p.Height, ii.Quantity) AS 面积
	                            , CAST(p.Price AS decimal(9, 2)) AS 价格, p.Remark AS 备注, dbo.GetTechnicsOfProductID(p.PKID) AS 工艺, c.CompanyName AS 客户
	                            , pj.PKID AS 计划ID, pj.ProjectName AS 计划
                            FROM InvoiceItem ii
	                            INNER JOIN Product p ON ii.ProductID = p.PKID
	                            INNER JOIN [Order] o ON p.OrderID = o.PKID
	                            INNER JOIN Project pj ON o.ProjectID = pj.PKID
	                            INNER JOIN Customer c ON pj.CustomerID = c.PKID
	                            INNER JOIN ProductType pt ON p.ProductTypeID = pt.PKID
                            WHERE ii.InvoiceID = @InvoiceID
                            ORDER BY pt.[Sequence], p.PKID ";
            try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@InvoiceID"].Value = nInvoiceID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataSet GetDataForInvoiceForm(string sInvoiceCode)
		{
			DataSet dataSet = new DataSet("InvoiceItem");
			//string str = "select p.PKID as 产品ID,pt.[Name] as 产品类型,pt.Unit as 类型单位,p.ProductCode as 产品编号,p.Width as 宽,p.SubtenseOfWidth as '宽(短边)',p.Height as 高,p.SubtenseOfHeight as '高(短边)',ii.Quantity as 发货数量,CAST(p.Price as decimal(9, 2)) AS 产品价格,p.Remark as 产品备注,dbo.GetTechnicsOfProductID(p.PKID) as 产品工艺,dbo.ProductTechnicsValue(p.PKID,ii.Quantity) as 工艺金额 from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID inner join ProductType as pt on p.ProductTypeID=pt.PKID inner join Invoice as i on ii.InvoiceID=i.PKID where i.Code=@InvoiceCode order by pt.[Sequence],convert( int, substring(p.PKID,1,6) ),convert( int, substring(p.PKID,charindex('.',p.PKID)+1,charindex('-',p.PKID)-charindex('.',p.PKID)-1) ),convert( int, substring(p.PKID,charindex('-',p.PKID)+1,len(p.PKID)) )";
            string str = @"SELECT p.PKID AS 产品ID, pt.[Name] AS 产品类型, pt.Unit AS 类型单位, p.ProductCode AS 产品编号, p.Width AS 宽
	                        , p.SubtenseOfWidth AS '宽(短边)', p.Height AS 高, p.SubtenseOfHeight AS '高(短边)', ii.Quantity AS 发货数量, CAST(p.Price AS decimal(9, 2)) AS 产品价格
	                        , p.Remark AS 产品备注, dbo.GetTechnicsOfProductID(p.PKID) AS 产品工艺
	                        , dbo.ProductTechnicsValue(p.PKID, ii.Quantity) AS 工艺金额
                        FROM InvoiceItem ii
	                        INNER JOIN Product p ON ii.ProductID = p.PKID
	                        INNER JOIN ProductType pt ON p.ProductTypeID = pt.PKID
	                        INNER JOIN Invoice i ON ii.InvoiceID = i.PKID
                        WHERE i.Code = @InvoiceCode
                        ORDER BY pt.[Sequence], p.PKID ";
            string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*ii.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID inner join InvoiceItem as ii on ii.ProductID=p.PKID inner join Invoice as i on ii.InvoiceID=i.PKID WHERE i.Code=@InvoiceCode group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			try
			{
				this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataTable LoadOrderItemForInvoice(string sProductID)
		{
			DataTable dataTable = new DataTable("orderItem");
			string cmdText = "SELECT p.PKID,pt.[Name],pt.Unit,p.ProductCode,p.Width,p.SubtenseOfWidth,p.Height,p.SubtenseOfHeight,p.Quantity,dbo.ItemSquare(p.Width,p.Height,p.Quantity),CAST(p.Price as decimal(9, 2)),p.Remark,dbo.GetTechnicsOfProductID(p.PKID) as 工艺,c.CompanyName,pj.PKID,pj.ProjectName FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID INNER JOIN [Order] AS o ON p.OrderID=o.PKID INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID WHERE p.PKID=@ProductID";
			try
			{
				if (this.selectCommand == null || this.selectCommand.Connection.State == ConnectionState.Closed)
				{
					this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
					this.selectCommand.Connection.Open();
					this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				}
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable LoadInvoiceItem(long nInvoiceID, string sProductID)
		{
			DataTable dataTable = new DataTable("InvoiceItem");
			string cmdText = "select p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit as 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth,p.Height AS 高,p.SubtenseOfHeight,ii.Quantity AS 数量,dbo.ItemSquare(p.Width,p.Height,ii.Quantity) as 面积,CAST(p.Price as decimal(9, 2)) AS 价格,p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺,c.CompanyName AS 客户,pj.PKID AS 计划ID,pj.ProjectName AS 计划 FROM InvoiceItem AS ii inner join Product AS p on ii.ProductID=p.PKID inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID inner join Customer AS c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE ii.InvoiceID=@InvoiceID and ProductID=@ProductID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceID", SqlDbType.BigInt));
				this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceID"].Value = nInvoiceID;
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public int GetProjectIDByProductID(string sProductID)
		{
			int result = -1;
			string cmdText = "select pj.PKID FROM Product AS p inner join [Order] AS o on p.OrderID=o.PKID inner join Project AS pj on o.ProjectID=pj.PKID WHERE p.PkID=@ProductID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				object value;
				if ((value = this.selectCommand.ExecuteScalar()) != null)
				{
					result = Convert.ToInt32(value);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool InsertInvoiceItem(long nInvoiceID, string sProductID, int nQuantity, int nOperatorID)
		{
			try
			{
				if (this.insertCommand == null || this.insertCommand.Connection.State == ConnectionState.Closed)
				{
					this.insertCommand = new SqlCommand("InsertInvoiceItem", new SqlConnection(AppSettings.ConnectionString));
					this.insertCommand.CommandType = CommandType.StoredProcedure;
					this.insertCommand.Connection.Open();
					this.insertCommand.Parameters.Add(new SqlParameter("@invoiceID", SqlDbType.BigInt));
					this.insertCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
					this.insertCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));
					this.insertCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				}
				this.insertCommand.Parameters["@invoiceID"].Value = nInvoiceID;
				this.insertCommand.Parameters["@productID"].Value = sProductID;
				this.insertCommand.Parameters["@quantity"].Value = nQuantity;
				this.insertCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateInvoiceItem(long nInvoiceID, string sProductID, int nQuantity, int nOperatorID)
		{
			try
			{
				this.updateCommand = new SqlCommand("UpdateInvoiceItem", new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.CommandType = CommandType.StoredProcedure;
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@invoiceID", SqlDbType.BigInt));
				this.updateCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));
				this.updateCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				this.updateCommand.Parameters["@invoiceID"].Value = nInvoiceID;
				this.updateCommand.Parameters["@productID"].Value = sProductID;
				this.updateCommand.Parameters["@quantity"].Value = nQuantity;
				this.updateCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DeleteInvoiceItem(long nInvoiceID, string sProductID, int nOperatorID)
		{
			try
			{
				if (this.deleteCommand == null || this.deleteCommand.Connection.State == ConnectionState.Closed)
				{
					this.deleteCommand = new SqlCommand("deleteInvoiceItem", new SqlConnection(AppSettings.ConnectionString));
					this.deleteCommand.CommandType = CommandType.StoredProcedure;
					this.deleteCommand.Connection.Open();
					this.deleteCommand.Parameters.Add(new SqlParameter("@invoiceID", SqlDbType.BigInt));
					this.deleteCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
					this.deleteCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				}
				this.deleteCommand.Parameters["@invoiceID"].Value = nInvoiceID;
				this.deleteCommand.Parameters["@productID"].Value = sProductID;
				this.deleteCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
